using Microsoft.Data.Sqlite;

namespace MultiAgentsClient.Service;

public class SqliteService()
{
    private SqliteConnection GetConnection(int userId, string tableName)
    {
        string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        string directoryPath;
        if (userId == 0)
        {
            directoryPath = Path.Combine(documentsPath, "MultiAgents");
        }
        else
        {
            directoryPath = Path.Combine(documentsPath, "MultiAgents", userId.ToString());
        }
        
        Directory.CreateDirectory(directoryPath);
        directoryPath = Path.Combine(directoryPath, $"{tableName}.db");

        string connectionString = $"Data Source={directoryPath}";
        SqliteConnection connection = new(connectionString);
        connection.Open();
        return connection;
    }

    private void EnsureTableExists(int userId, string tableName)
    {
        using SqliteConnection connection = GetConnection(userId, tableName);

        string createTableCommand = $@"
                CREATE TABLE IF NOT EXISTS {tableName} (
                    [Key] TEXT PRIMARY KEY,
                    [Value] TEXT
                );";

        using SqliteCommand command = new(createTableCommand, connection);
        command.ExecuteNonQuery();
    }

    public void UpdateDictionaryValue(int userId, string tableName, string key, string value)
    {
        EnsureTableExists(userId, tableName);

        using SqliteConnection connection = GetConnection(userId, tableName);

        string upsertCommand = $@"
                INSERT OR REPLACE INTO {tableName} (Key, Value)
                VALUES (@key, @value);";

        using SqliteCommand command = new(upsertCommand, connection);
        command.Parameters.AddWithValue("@key", key);
        command.Parameters.AddWithValue("@value", value);

        command.ExecuteNonQuery();
    }

    public Dictionary<string, string> GetDictionary(int userId, string tableName)
    {
        EnsureTableExists(userId, tableName);

        using SqliteConnection connection = GetConnection(userId, tableName);

        string selectCommand = $@"
            SELECT [Key], [Value]
            FROM {tableName};";

        using SqliteCommand command = new(selectCommand, connection);
        using SqliteDataReader reader = command.ExecuteReader();

        Dictionary<string, string> dictionary = [];
        while (reader.Read())
        {
            string key = reader.GetString(0);
            string value = reader.GetString(1);
            dictionary[key] = value;
        }

        return dictionary;
    }

    public void ClearTable(int userId, string tableName)
    {
        EnsureTableExists(userId, tableName);

        using SqliteConnection connection = GetConnection(userId, tableName);

        string clearTableCommand = $@"
        DELETE FROM {tableName};";

        using SqliteCommand command = new(clearTableCommand, connection);
        command.ExecuteNonQuery();
    }

    //public static void CreateTable(int userId, string tableName, string createTableSql)
    //{
    //    using var connection = GetConnection(userId, tableName);
    //    using var command = connection.CreateCommand();

    //    command.CommandText = createTableSql;
    //    command.ExecuteNonQuery();
    //}

    //public static int AddRow<TDatabaseClass>(int userId, string tableName, TDatabaseClass item)
    //{
    //    using var connection = GetConnection(userId, tableName);
    //    FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw new InvalidOperationException();
    //    string sqlFirst = $"INSERT INTO {tableName} (";
    //    string sqlSecond = "VALUES (";
    //    var parameters = new Dictionary<string, object>();

    //    for (int i = 0; i < infos.Length; i++)
    //    {
    //        string name = infos[i].Name;
    //        object? value = infos[i].GetValue(item);

    //        if (name == "_id" || name == "dbException" || value == null) continue;

    //        name = char.ToUpper(name[1]) + name[2..];
    //        sqlFirst += $"{name}, ";
    //        sqlSecond += $"@{name}, ";

    //        if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(List<>))
    //        {
    //            parameters.Add($"@{name}", JsonSerializer.Serialize(value));
    //        }
    //        else
    //        {
    //            parameters.Add($"@{name}", value);
    //        }
    //    }

    //    sqlFirst = sqlFirst[..^2] + ") ";
    //    sqlSecond = sqlSecond[..^2] + ");";

    //    string sql = sqlFirst + sqlSecond;

    //    using (var command = connection.CreateCommand())
    //    {
    //        command.CommandText = sql;
    //        foreach (var param in parameters)
    //        {
    //            command.Parameters.AddWithValue(param.Key, param.Value);
    //        }

    //        command.ExecuteNonQuery();
    //        return (int)connection.LastInsertRowId;
    //    }
    //}

    //public static bool DeleteRow<TDatabaseClass>(int userId, string tableName, TDatabaseClass item)
    //{
    //    using var connection = GetConnection(userId, tableName);
    //    FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw new InvalidOperationException();
    //    string sql = $"DELETE FROM {tableName} WHERE ";
    //    var parameters = new Dictionary<string, object>();

    //    for (int i = 0; i < infos.Length; i++)
    //    {
    //        string name = infos[i].Name;
    //        object? value = infos[i].GetValue(item);
    //        if (name == "dbException" || value == null) continue;

    //        name = char.ToUpper(name[1]) + name[2..];
    //        sql += $"{name} = @{name} AND ";
    //        parameters.Add($"@{name}", value);
    //    }

    //    sql = sql[..^5];

    //    using var command = connection.CreateCommand();
    //    command.CommandText = sql;
    //    foreach (var param in parameters)
    //    {
    //        command.Parameters.AddWithValue(param.Key, param.Value);
    //    }

    //    int affectedRows = command.ExecuteNonQuery();
    //    return affectedRows > 0;
    //}

    //public static List<TDatabaseClass> QueryRows<TDatabaseClass>(int userId, string tableName, TDatabaseClass item) where TDatabaseClass : new()
    //{
    //    using var connection = GetConnection(userId, tableName);
    //    FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw new InvalidOperationException();
    //    string sql = $"SELECT * FROM {tableName} WHERE ";
    //    var parameters = new Dictionary<string, object>();

    //    for (int i = 0; i < infos.Length; i++)
    //    {
    //        string name = infos[i].Name;
    //        object? value = infos[i].GetValue(item);
    //        if (name == "dbException" || value == null) continue;

    //        name = char.ToUpper(name[1]) + name[2..];
    //        sql += $"{name} = @{name} AND ";
    //        parameters.Add($"@{name}", value);
    //    }

    //    if (parameters.Count == 0)
    //    {
    //        sql = sql[..^7]; // No WHERE clause
    //    }
    //    else
    //    {
    //        sql = sql[..^5]; // Remove last AND
    //    }

    //    using var command = connection.CreateCommand();
    //    command.CommandText = sql;
    //    foreach (var param in parameters)
    //    {
    //        command.Parameters.AddWithValue(param.Key, param.Value);
    //    }

    //    using (var reader = command.ExecuteReader())
    //    {
    //        var result = new List<TDatabaseClass>();
    //        while (reader.Read())
    //        {
    //            var row = new TDatabaseClass();
    //            for (int i = 0; i < infos.Length; i++)
    //            {
    //                object value = reader[infos[i].Name];
    //                infos[i].SetValue(row, value);
    //            }
    //            result.Add(row);
    //        }
    //        return result;
    //    }
    //}
}
